#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   data.py
@Time    :   2020/09/13 21:13:43
@Author  :   Wicos
@Version :   1.0
@Contact :   wicso@wicos.cn
@License :   (C)Copyright 2017-2018, Liugroup-NLPR-CASIA
@Blog    :   https://www.wicos.me
'''

# here put the import lib
import pymysql as sql


class CON(object):
    def __init__(self, ip, dbname, username, password):
        self.ip = ip
        self.dbname = dbname
        self.username = username
        self.password = password
        self.connect()

    def connect(self):
        self.db = sql.connect(host=self.ip, user=self.username,
                              passwd=self.password, db=self.dbname)
        self.cursor()

    def cursor(self):
        self.cursor = self.db.cursor()

    def close(self):
        self.db.close()

    def creat_table(self):
        todo = """CREATE TABLE receive (
         id  TEXT,
         date  TEXT,
         type INT,
         content LONGTEXT)"""
        self.cursor.execute(todo)

    def add(self, id, date, type, content):
        todo = """INSERT INTO receive(id,date, type, content) VALUES ("{}", "{}", {}, "{}")""".format(
            id, date, type, content)
        print(todo)
        try:
            self.cursor.execute(todo)
            self.db.commit()
        except:
            self.db.rollback()
        self.close()
        return 0

    def get_data(self,nums):
        todo = "SELECT * FROM receive LIMIT {}".format(nums)
        try:
            self.cursor.execute(todo)
            results = self.cursor.fetchall()
            bc = []
            for row in results:
                x = {
                    "id":row[0],
                    "date":row[1],
                    "type":row[2],
                    "content":row[3]
                }
                if x["type"] == 0 and len(x['content'])>50:
                    x["content"] = x["content"][:50] + "......"
                bc.append(x)
            return bc
        except:
            print("Error: unable to fetch data")
        self.close()

    def delete_from_id(self,signal_id):
        todo = "DELETE FROM receive WHERE id > '{}'".format(signal_id)
        try:
            self.cursor.execute(todo)
            self.db.commit()
        except:
            self.db.rollback()
        self.close()

    def find_from_type(self, type,offset=None,rows=None):
        todo = "SELECT * FROM receive WHERE type = {}".format(type)
        if offset != None and rows != None:
            todo = "SELECT * FROM receive WHERE type = {} LIMIT {},{}".format(type,offset,rows)
        if rows != None and offset == None:
            todo = "SELECT * FROM receive WHERE type = {} LIMIT {}".format(type,rows)
        print(todo)
        try:
            self.cursor.execute(todo)
            results = self.cursor.fetchall()
            bc = []
            for row in results:
                x = {
                    "id":row[0],
                    "date":row[1],
                    "type":row[2],
                    "content":row[3]
                }
                if x["type"] == 0 and len(x['content'])>50:
                    x["content"] = x["content"][:50] + "......"
                bc.append(x)
            return bc
        except:
            print("Error: unable to fetch data")
        self.close()

    def find_from_id(self, id):
        todo = "SELECT * FROM receive WHERE id = {}".format(id)
        print(todo)
        try:
            self.cursor.execute(todo)
            results = self.cursor.fetchall()
            bc = []
            for row in results:
                x = {
                    "id":row[0],
                    "date":row[1],
                    "type":row[2],
                    "content":row[3]
                }
                bc.append(x)
            return bc
        except:
            print("Error: unable to fetch data")
        self.close()
"""
a = CON("202.61.130.50","send","send","13689920878")
b=a.get_data(10)
print(b)"""